﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using MODEL;
using System.Data;
namespace DAL
{
    public class DalDic
    {
        public int Insert(Dic dic)
        {
            string sql = "insert into dic(goodsname,classid,classname,goodsmodel,goodsspe,goodsunit,goodprice,goodspy) values(@name,@classid,@classname,@model,@spe,@unit,@price,@goodspy)";
            OleDbParameter[] pams = new OleDbParameter[]
                {
                    new OleDbParameter("@name",dic.GoodsName),
                    new OleDbParameter("@classid",dic.ClassId),
                    new OleDbParameter("@classname",dic.ClassName),
                    new OleDbParameter("@model",dic.GoodsModel),
                    new OleDbParameter("@spe",dic.GoodsSpe),
                    new OleDbParameter("@unit",dic.GoodsUnit),
                    new OleDbParameter("@price",dic.GoodsPrice),
                    new OleDbParameter("goodspy",dic.Pingying)
                };
            return AccessHelp.ExecteNonQuery(sql,pams);
        }
        public List<Dic> GetAll()
        {
            string sql = "select id,classid,classname,goodsname,goodsmodel,goodsspe,goodsunit,goodprice,goodspy from dic where del=0";
            DataTable dt = AccessHelp.ExecuteReader(sql, null);
            return TableToList(dt);
        }

        public List<Dic> GetByKey(string key)
        {
            string sql = "select id,classid,classname,goodsname,goodsmodel,goodsspe,goodsunit,goodprice,goodspy from dic where del=0 and goodsname like @name";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@name", "%"+key+"%"));
            return TableToList(dt);
        }

        public List<Dic> TableToList(DataTable dt)
        {
            List<Dic> dics = new List<Dic>();
            foreach (DataRow row in dt.Rows)
            {
                Dic dic = new Dic();
                dic.Id = Convert.ToInt32(row[0]);
                dic.ClassId = Convert.ToInt32(row[1]);
                dic.ClassName = row[2].ToString();
                dic.GoodsName = row[3].ToString();
                dic.GoodsModel = row[4].ToString();
                dic.GoodsSpe = row[5].ToString();
                dic.GoodsUnit = row[6].ToString();
                dic.GoodsPrice = Convert.ToDouble(row[7]);
                dic.Pingying = row[8].ToString();
                dics.Add(dic);
            }
            return dics;
        }

        public int Delete(int id)
        {
            string sql = "update dic set del=1 where id=@id";
            return AccessHelp.ExecteNonQuery(sql,new OleDbParameter("@id",id));
        }

        public List<Dic> GetByPY(string namePY)
        {
            string sql = "select id,classid,classname,goodsname,goodsmodel,goodsspe,goodsunit,goodprice,goodspy from dic where del=0 and goodspy like @py order by classid,id";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@py", "%" + namePY + "%"));
            return TableToList(dt);
        }
        public Dic GetById(int id)
        {
            string sql = "select id,classid,classname,goodsname,goodsmodel,goodsspe,goodsunit,goodprice,goodspy from dic where del=0 and id=@id";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@id",id));
            return TableToList(dt).FirstOrDefault();
        }

        public int Update(Dic dic)
        {
            string sql = "update dic set classid=@classid,classname=@classname,goodsname=@goodsname,goodsmodel=@goodsmodel,goodsspe=@goodsspe,goodsunit=@goodsunit,goodprice=@goodprice,goodspy=@goodspy where id=@id";
            OleDbParameter[] pms = new OleDbParameter[]
            {
              new OleDbParameter("@classid",dic.ClassId),
              new OleDbParameter("@classname",dic.ClassName),
              new OleDbParameter("@goodsname",dic.GoodsName),
              new OleDbParameter("@goodsmodel",dic.GoodsModel),
              new OleDbParameter("@goodsspe",dic.GoodsSpe),
              new OleDbParameter("@goodsunit",dic.GoodsUnit),
              new OleDbParameter("@goodprice",dic.GoodsPrice),
              new OleDbParameter("@goodspy",dic.Pingying),
              new OleDbParameter("@id",dic.Id)
            };
            return AccessHelp.ExecteNonQuery(sql,pms);
        }

        public int IsExist(Dic dic)
        {
            string sql = "select id from dic where goodsname=@goodsname and goodsmodel=@goodsmodel and goodsspe=@goodsspe and goodsunit=@goodsunit";
            OleDbParameter[] pms = new OleDbParameter[]
            {
              new OleDbParameter("@goodsname",dic.GoodsName),
              new OleDbParameter("@goodsmodel",dic.GoodsModel),
              new OleDbParameter("@goodsspe",dic.GoodsSpe),
              new OleDbParameter("@goodsunit",dic.GoodsUnit)            
            };
            return Convert.ToInt32(AccessHelp.ExecuteScalar(sql,pms));
        }
    }
}
